package net.hn.hnms.biz.safety.trinity.dao;

import net.hn.hnms.biz.safety.trinity.entity.RiskControlList;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * @author 智呈物联 nzw
 * @date 2022/8/4
 */
@Repository
public interface RiskControlListDao extends JpaRepository<RiskControlList,String> {

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\trisk_control_list\n" +
            "WHERE 1=1 and \n" +
            "\t CASE when ?1 != '' then trim(mine_code) = ?1 else 1=1 end and to_char(data_time,'yyyy-MM-dd') >= ?2",nativeQuery = true)
    int countAll(String mineCode,String dataTime);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\trisk_control_list \n" +
            "WHERE\n" +
            "\trisk_level =?1 \n" +
            "\tand CASE when ?2 != '' then trim(mine_code) = ?2 else 1=1 end and CASE when ?3 != '' then to_char(data_time,'yyyy-MM-dd') >= ?3 else 1=1 end",nativeQuery = true)
    int countAllByRiskLevel(String riskLevel,String mineCode,String dataTime);

    @Query(value = "SELECT\n" +
            "\trisk_level as riskLevel,\n" +
            "\trisk_id as riskId,\n" +
            "\trisk_description as description\n" +
            "FROM\n" +
            "\trisk_control_list " +
            "WHERE 1=1 and \n" +
            "\tCASE when ?1 != '' then trim(mine_code) = ?1 else 1=1 end",nativeQuery = true)
    List<Map<String,Object>> queryAll(String mineCode);

    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\trisk_control_list\n "+
            "WHERE 1=1 and \n" +
            " CASE when ?1 != '' then  trim(mine_code) = ?1 else 1=1 end"+
            " AND CASE when ?2 != '' then  trim(company_code) = ?2 else 1=1 end",nativeQuery = true)
    int countAllByMineCodeOrCompanyCode(String mineCode,String companyCode);


    @Query(value = "SELECT COUNT\n" +
            "\t( * ) \n" +
            "FROM\n" +
            "\trisk_control_list \n" +
            "WHERE\n" +
            "\trisk_level =?1 \n"+
            " AND CASE when ?2 != '' then company_code = ?2 else 1=1 end",nativeQuery = true)
    int countAllByRiskLevel(String riskLevel ,String companyCode);

    @Query(value = "SELECT\n" +
            "\t\"count\" ( 1 ),\n" +
            "\trisk_level \n" +
            "FROM\n" +
            "\trisk_control_list \n" +
            "GROUP BY\n" +
            "\trisk_level",nativeQuery = true)
    List<Map<String,Object>> riskLevel();


    @Query(value = "SELECT\n" +
            "\tmine_code \n" +
            "FROM\n" +
            "\trisk_control_list \n" +
            "GROUP BY\n" +
            "\tmine_code",nativeQuery = true)
    List<String> riskMineCode();

    @Query(value = "SELECT\n" +
            "\t\"count\" ( 1 ),\n" +
            "\trisk_level \n" +
            "FROM\n" +
            "\trisk_control_list \n" +
            "WHERE\n" +
            "\tmine_code = ? \n" +
            "GROUP BY\n" +
            "\trisk_level",nativeQuery = true)
    List<Map<String,Object>> riskLevelByMineCode(String mineCode);

    @Query(value = "SELECT\n" +
            "\t\"count\" ( 1 ),\n" +
            "\trisk_level \n" +
            "FROM\n" +
            "\trisk_control_list \n" +
            "GROUP BY\n" +
            "\trisk_level",nativeQuery = true)
    List<Map<String,Object>> getRiskLevelYear(String dataTime);

    @Query(value = "SELECT\n" +
            "\t\"count\" ( 1 ),\n" +
            "\trisk_class \n" +
            "FROM\n" +
            "\trisk_control_list \n" +
            "GROUP BY\n" +
            "\trisk_class",nativeQuery = true)
    List<Map<String,Object>> getRiskType();
}
